package com.a51work6.qq.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDAO {
	// query all user information
	public List<Map<String, String>> findAll(){
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		// sql statement
		String sql = "select user_id,user_pwd,user_name,user_icon from user where user_id";
		try(
				// 1. create database connection
				Connection conn = DBHelper.getConnection();
				// 2. create statement object
				PreparedStatement pstmt = conn.prepareStatement(sql);
				// 3. execute query
				ResultSet rs = pstmt.executeQuery();
		){
			while(rs.next()) {
				Map<String, String> row = new HashMap<String, String>();
				row.put("user_id", rs.getString("user_id"));
				row.put("user_pwd", rs.getString("user_pwd"));
				row.put("user_name", rs.getString("user_name"));
				row.put("user_icon", rs.getString("user_icon"));
				
				list.add(row);
			}
		}catch(SQLException e) {
			
		}
		return list;
	}
	// query users' information by id
	public Map<String, String> findByID(String id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select user_id,user_pwd,user_name,user_icon from user where user_id = ?";
		
		try {
			// 1. create database connection
			conn = DBHelper.getConnection();
			// 2. create statement object
			pstmt = conn.prepareStatement(sql);
			// 3. execute query
			pstmt.setObject(1 ,id);
			rs = pstmt.executeQuery();
			// 4. iterate result
			if(rs.next()) {
				Map<String, String> row = new HashMap<String, String>();
				row.put("user_id", rs.getString("user_id"));
				row.put("user_name", rs.getString("user_name"));
				row.put("user_pwd", rs.getString("user_pwd"));
				row.put("user_icon", rs.getString("user_icon"));
				
				return row;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs != null) {
				try {
					rs.close();
				}catch(SQLException e) {
					
				}
			}
			if(pstmt != null) {
				try {
					pstmt.close();
				}catch(SQLException e) {
					
				}
			}
			if(conn != null) {
				try {
					conn.close();
				}catch(SQLException e) {
					
				}
			}
		}
		return null;
	}
	// query friends' list
	public List<Map<String, String>> findFriends(String id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		List<Map<String, String>> friends = new ArrayList<Map<String, String>>();
		String sql = "select * from user where user_id in"
				+ "(select user_id2 as user_id from friend where user_id1 = ?)"
				+ "or user_id in (select user_id1 as user_id from friend where user_id2 = ?)";
		try {
			// 1. create database connection
			conn = DBHelper.getConnection();
			// 2. create statement object
			pstmt = conn.prepareStatement(sql);
			// 3. bind parameters
			pstmt.setString(1, id);
			pstmt.setString(2, id);
			// 4. execute query
			rs = pstmt.executeQuery();
			// 5. iterate result
			while(rs.next()) {
				Map<String, String> row = new HashMap<String, String>();
				row.put("user_id", rs.getString("user_id"));
				row.put("user_name", rs.getString("user_name"));
				row.put("user_pwd", rs.getString("user_pwd"));
				row.put("user_icon", rs.getString("user_icon"));
				
				friends.add(row);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs != null) {
				try {
					rs.close();
				}catch(SQLException e) {
					
				}
			}
			if(pstmt != null) {
				try {
					pstmt.close();
				}catch(SQLException e) {
					
				}
			}
			if(conn != null) {
				try {
					conn.close();
				}catch(SQLException e) {
					
				}
			}
		}
		return friends;
	}
}
